set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dwm.dwm_analysis_target_network_sum_week_dt
select
     date_sub(next_day(stat_date,'mo'),7) as stat_date
    ,network_type
    ,network_code
    ,network_name
    ,franchisee_code
    ,franchisee_name
    ,agent_code
    ,agent_name
    ,virt_code
    ,virt_name
    ,kpi_code
    ,kpi_name
    ,kpi_type
    ,sum(kpi_fz) as kpi_fz
    ,sum(kpi_fm) as kpi_fm
    ,sum(kpi_fz) / sum(kpi_fm) as kpi_values
    ,kpi_param
    ,agent_principal_name
    ,franchisee_principal_name
    ,principal_name
    ,kpi_alias
    ,date_sub(next_day(stat_date,'mo'),7)  as dt
from jms_dwm.dwm_analysis_target_network_sum_day_dt tar
where dt between (
    select min(if(weekday_eg='Monday',date_mid_desc,'9999-12-31'))
    from jms_dim.dim_date_info_base
    where date_mid_desc between date_add('{{ execution_date | cst_ds }}',-38) and '{{ execution_date | cst_ds }}'
) and '{{ execution_date | cst_ds }}'
group by date_sub(next_day(stat_date,'mo'),7)
        ,network_type
        ,network_code
        ,network_name
        ,franchisee_code
        ,franchisee_name
        ,agent_code
        ,agent_name
        ,virt_code
        ,virt_name
        ,kpi_code
        ,kpi_name
        ,kpi_type
        ,kpi_param
        ,agent_principal_name
        ,franchisee_principal_name
        ,principal_name
        ,kpi_alias
distribute by dt,pmod(hash(rand()),1)
;

